package javagprssrv.DataBase;

import com.sun.rowset.CachedRowSetImpl;
import java.sql.ResultSet;
import javagprssrv.StructClass.structDBInfo;
import javagprssrv.JGS_Param;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Arrays;
import java.util.Date;
import java.util.List;
import java.util.Map;
import java.util.logging.Level;
import org.apache.log4j.Logger;
import org.dusin.commonutil.DateUtil;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.jdbc.core.JdbcTemplate;

public class DBSQLExecute {

    private static JdbcTemplate jdbcTemplate;
    private static ApplicationContext ctx;

    static Logger logger = Logger.getLogger(DBSQLExecute.class);
    static{
        ctx = new ClassPathXmlApplicationContext("org/dusin/dyjccomm/resource/applicationContext.xml");
        jdbcTemplate = (JdbcTemplate) ctx.getBean("jdbcTemplate");
    }

    public DBSQLExecute() {
        
    }

    public DBSQLExecute(final java.lang.Object DBStatement) {
    }

    public void SQL_Update(final int idxDBConnectionPool, final java.lang.String SQLString) {
        try {
            logger.info("准备执行SQL语句："+SQLString);
            jdbcTemplate.execute(SQLString);
        } catch (Exception e) {
            e.printStackTrace();
            logger.error(e.getMessage());
        }
    }

    public boolean doCreateBatch(List<Object[]> beans) {
        boolean flag = true;
        String sql = "insert into t_5m(deviceId,pointId,dataTime,voltage) values(?,?,?,?)";
        logger.debug(sql);
        int[] rows = jdbcTemplate.batchUpdate(sql, beans);
        if (rows == null) {
            flag = false;
        }
        return flag;
    }

    public boolean doCreate5m(Object[] bean) {
        boolean flag = true;
        String sql = "insert into t_5m(deviceId,pointId,dataTime,voltage) values(?,?,?,?)";
        logger.debug(sql+Arrays.asList(bean));
        int row = jdbcTemplate.update(sql, bean);
        if (row < 0) {
            flag = false;
        }
        return flag;
    }
    
    public boolean doUpdateHeartBeatBatch(List<Object[]> beans){
        boolean flag = true;        
        String sql = "UPDATE t_device set LastTalk=? where AdminAreaCode=? and TerminalNO=?  and ZBM=?";
        logger.debug(sql+"size:"+beans.size());
        int[] rows = jdbcTemplate.batchUpdate(sql, beans);        
        if (rows == null) {
            flag = false;
        }
        /*jdbcTemplate.batchUpdate(sql, beans,30,new BatchPreparedStatementSetter(){

            @Override
            public void setValues(PreparedStatement ps, int i) throws SQLException {
                
            }

            @Override
            public int getBatchSize() {
                
            }
        });*/
        return flag;
    }
   
    
    public java.sql.ResultSet SQL_Query(final int idxDBConnectionPool, final java.lang.String SQLString) {
        ResultSet rs = null;
        CachedRowSetImpl  rowSet=null;
        Connection conn = null;
        Statement st = null;
        
        try {
            conn = jdbcTemplate.getDataSource().getConnection();
            st = conn.createStatement();
            rs = st.executeQuery(SQLString);
            rowSet = new CachedRowSetImpl();
            rowSet.populate(rs);
        } catch (java.lang.Exception e) {
            e.printStackTrace();
            javagprssrv.JGS_Param.myLog.ShowLog("(SQL=" + SQLString + "@DBSQLExecute.SQL_Query()) 数据库操作组件发生错误 ！！！请检查。", true, true);
        }finally{
            if(rs!=null){
                try {
                    rs.close();
                } catch (SQLException ex) {
                    logger.error(ex.getMessage());
                }
            }
            if(st!=null){
                try {
                    st.close();
                } catch (SQLException ex) {
                    logger.error(ex.getMessage());
                }
            }
            if(conn!=null){
                try {
                    conn.close();
                } catch (SQLException ex) {
                    logger.error(ex.getMessage());
                }
            }
        }
        return rowSet;
    }
    
    public String getPointIDByDeviceID(String deviceID){
        String pid = "";
        String deviceId = deviceID.trim();
        String sql = "select ID from t_Point where DeviceID=? ";
        try{
            pid=jdbcTemplate.queryForObject(sql, String.class,deviceId);
        }catch(Exception e){
            e.printStackTrace();
        }
        return pid;        
    }
    
    public int findCountRsj(String deviceId,String dataYear,String dataMonth,String dataDay){
        String sql =  "select count(*) from T_RSJ where DeviceID=? and  dataYear=? and dataMonth=? and dataDay=?";
        int count = jdbcTemplate.queryForObject(sql, Integer.class,deviceId,dataYear,dataMonth,dataDay);
        return count;
    }
    
    public boolean insertRsj(String deviceId,String pointId,String dataYear,String dataMonth,String dataDay,String time,String hour,String dyz){
        String sql = "insert into T_RSJ(DeviceID,PointID,dataYear,dataMonth,dataDay,CreateTime,H" + hour + ") values (?,?,?,?,?,?,?)";
        int rows = 0;
        try{
            logger.info("准备插入日数据:"+Arrays.asList(new Object[]{deviceId,pointId,dataYear,dataMonth,dataDay,hour,time,dyz}));
            rows = jdbcTemplate.update(sql, deviceId,pointId,dataYear,dataMonth,dataDay,time,dyz);
        }catch(Exception e){
            e.printStackTrace();
            logger.error(e.getMessage());
        }
        return rows != 0;
    }
    
    public boolean updateRsj(String deviceId,String dataYear,String dataMonth,String dataDay,String hour,String dyz){
        String sql = "UPDATE T_RSJ set h"+hour+"=? where datayear=? and datamonth=? and dataDay=? and deviceId=?";
        int rows = 0;
        logger.info("准备更新日数据:"+Arrays.asList(new Object[]{dyz,dataYear,dataMonth,dataDay,hour,deviceId}));
        rows = jdbcTemplate.update(sql, dyz,dataYear,dataMonth,dataDay,deviceId);
        return rows!=0;
    }
    
    public int findCountYsj(String deviceId,String dataYear,String dataMonth){
        String sql =  "select count(*) from T_YSJ where DeviceID=? and  dataYear=? and dataMonth=?";
        int count = jdbcTemplate.queryForObject(sql, Integer.class,deviceId,dataYear,dataMonth);
        return count;
    }
    
    //sql 中VL.code vcode字段名称修改成vcode,为电压等级编码，否则会和point中的code值冲突，取得的是point表中的code
    public Map<String,Object> findYbInfo(String areaAdminCode,String terminalNo,String channel){
        String sql =  "select VL.code vcode,device.* from V_pointExtend device left outer join T_VoltageLevel VL on VL.id=device.VoltageLevelID  where device.AdminAreaCode=? and device.TerminalNO=? and device.CHANNEL=?";
        logger.info(sql+"?"+Arrays.asList(new Object[]{areaAdminCode,terminalNo,channel}));
        try{
            return jdbcTemplate.queryForMap(sql, areaAdminCode,terminalNo,channel);
        }catch(Exception e){            
            e.printStackTrace();
            logger.error("根据一AdminAreaCode和TerminalNo和channel未找到仪表信息:"+Arrays.asList(new Object[]{areaAdminCode,terminalNo,channel})+e.getMessage());
            return null;
        }
    }
    
    public Map<String,Object> findYbInfo(String areaAdminCode,String terminalNo){
        String sql =  "select * from V_DeviceExtend where AdminAreaCode=? and TerminalNO=?";
        logger.info(sql+"?"+Arrays.asList(new Object[]{areaAdminCode,terminalNo}));
        try{
            return jdbcTemplate.queryForMap(sql, areaAdminCode,terminalNo);
        }catch(Exception e){
            e.printStackTrace();
            logger.error("根据一AdminAreaCode和TerminalNo未找到仪表信息:"+Arrays.asList(new Object[]{areaAdminCode,terminalNo})+e.getMessage());
            return null;
        }
    }
    
    public Map<String,Object> findYbInfo(String uniqueCode){
        String sql =  "select VL.code,d.uniqueCode,device.* from V_pointExtend device left outer join T_VoltageLevel VL on VL.id=device.VoltageLevelID left outer join T_Device d on d.id=device.DeviceId  where d.uniqueCode=?";
        logger.info(sql+"?"+uniqueCode);
        try{
            return jdbcTemplate.queryForMap(sql, uniqueCode);
        }catch(Exception e){
            logger.error("根据uniqeCode未找到仪表信息:"+uniqueCode+e.getMessage());
            e.printStackTrace();            
            return null;
        }
        
    }
    public Map<String,Object> findYbInfo2(String deviceId){
        String sql =   "select * from t_Device where ID=?" ;
        logger.info(sql+"?"+deviceId);
        try{
            return jdbcTemplate.queryForMap(sql, deviceId);
        }catch(Exception e){
            logger.error("根据一deviceId未找到仪表信息:"+deviceId);
            e.printStackTrace();            
            return null;
        }
    }
    public Map<String,Object> findModel(String deviceId){
        String sql = "select device.*,model.model from T_Device device left outer join T_Model model on model.id=device.ModelID where device.id=?";
        logger.info(sql+"?"+deviceId);
        try{
            return jdbcTemplate.queryForMap(sql, deviceId);
        }catch(Exception e){
            logger.error("根据一deviceId未找到仪表型号:"+deviceId);
            e.printStackTrace();            
            return null;
        }
    }
    
    public List<Map<String,Object>> findAllYbInfoByCond(String cond){
        String sql = "select * from V_DeviceExtend "+cond;
        return jdbcTemplate.queryForList(sql);
    }
    
}
